﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;

namespace MHPL_DoTrangSuc.Data
{
    class KetNoi
    {
        string strconnect = "Data Source=HANHLUONG-PC;AttachDbFilename=C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\MHPL_BanTrangSuc.mdf;Integrated Security=True";
        SqlConnection sqlcon;
        SqlCommand sqlcom;
        SqlDataAdapter sqldata;
        SqlDataReader sqldr;
        DataSet ds = new DataSet();

         #region "Lay Connection................"
        public static string path = System.Windows.Forms.Application.StartupPath;
           
# endregion

            #region ket noi
            public void ketnoi()
        {

            sqlcon = new SqlConnection(strconnect);
            sqlcon.Open();
        }
        #endregion

        #region ngat ket noi
        void ngatketnoi()
        {
            sqlcon.Close();
        }
        #endregion

        #region thuc thi ket noi
        public void thucthiketnoi(string strsql)
        {

            ketnoi();
            sqlcom = new SqlCommand(strsql, sqlcon);
            sqlcom.ExecuteNonQuery();
            ngatketnoi();
        }
        #endregion

        #region kiem tra
        public bool kiemtra(string dauvao, string strsql, byte chiso)
        {
            bool kt = false;
            ketnoi();
            sqlcom = new SqlCommand(strsql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (sqldr[chiso].ToString().ToLower() == dauvao.ToLower())
                    kt = true;
            }
            ngatketnoi();
            return kt;

                  }
        #endregion






        internal bool ThucHienCauTruyVan(string truyvan, IDictionary<string, string> d)
        {
            try
            {
                if (sqlcon.State == ConnectionState.Closed)
                    sqlcon.Open();
                SqlCommand cmd = new SqlCommand(truyvan, sqlcon);
                if (d != null)
                {
                    IDictionaryEnumerator en = (IDictionaryEnumerator)d.GetEnumerator();
                    while (en.MoveNext())
                    {
                        SqlParameter param = new SqlParameter();
                        param.ParameterName = en.Key.ToString();
                        param.Value = en.Value.ToString();
                        cmd.Parameters.Add(param);
                    }
                }
                if (cmd.ExecuteNonQuery() <= 0)
                {
                    sqlcon.Close();
                    return false;
                }
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #region luu
        public void Luu(string sql)
        {
            SqlCommand sqlcom = new SqlCommand();
            sqlcom.CommandText = sql;
            sqlcom.Connection = sqlcon;
            sqlcom.ExecuteNonQuery();
        }
        #endregion

        public SqlDataReader ThucHienReader(string sql)
        {
            SqlCommand cmd = new SqlCommand();
            SqlDataReader reader;
            cmd.Connection = sqlcon;
            cmd.CommandText = sql;
            reader = cmd.ExecuteReader();
            return reader;
        }

        #region load com bobox
        public void loadcombobox(ComboBox cb, string strselect, byte chiso)
        {
            ketnoi();
            sqlcom = new SqlCommand(strselect, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Items.Add(sqldr[chiso].ToString());
            }
            ngatketnoi();
        }
        #endregion

        public void LoadComBoBoxMaDH(ComboBox c1, string query1, string thuoctinhDH)
        {
            
            sqlcon = new SqlConnection(strconnect);
            sqlcon.Open();
            sqldata = new SqlDataAdapter(query1, sqlcon);
            DataTable dt1 = new DataTable();
            sqldata.Fill(dt1);
            c1.DataSource = dt1;
            c1.DisplayMember = thuoctinhDH;
            sqlcon.Close();
        }

       
        public void loadtextbox(TextBox cb, string strselect)
        {
            ketnoi();
            sqlcom = new SqlCommand(strselect, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                cb.Text = sqldr[0].ToString();
            }
            ngatketnoi();
        }

        public bool kttrungkhoa(string dauvao, string strsql)
        {
            bool ok = false;
            ketnoi();
            sqlcom = new SqlCommand(strsql, sqlcon);
            sqldr = sqlcom.ExecuteReader();
            while (sqldr.Read())
            {
                if (sqldr[0].ToString().ToLower() == dauvao.ToLower())
                    ok = true;
            }
            ngatketnoi();
            return ok;
        }






       
    }

    }


